package dao;

import pojo.Student;
import utils.DbUtils;
import utils.StudentEnum;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DbUtils implements StudentDao{
    @Override
    public List<Student> selectAll(String stuName,String stuNo,Integer sex,Integer pageIndex,Integer pageSize) throws SQLException {
        StringBuffer sql = new StringBuffer(" select * from student where 1=1 and state!=4 ");
        List param = new ArrayList();
        //模糊查询
        if (stuName != null && stuName!=""){
            sql.append(" and stuname like '%"+stuName+"%' ");
        }
        if (stuNo != null && stuNo!=""){
            sql.append(" and stuno like '%"+stuNo+"%' ");
        }
        if (sex!=-1 && sex != null){
            sql.append(" and sex like '%"+sex+"%' ");
        }
        //分页
        sql.append(" limit ?,? ");
        param.add((pageIndex-1)*pageSize);
        param.add(pageSize);
        ResultSet query = query(sql.toString(), param);
        ArrayList<Student> students = new ArrayList<>();
        while (query.next()){
            Student student = new Student();
            student.setStuId(query.getInt("stuid"));
            student.setStuName(query.getString("stuname"));
            student.setStuNo(query.getString("stuno"));
            student.setSex(query.getInt("sex"));
            student.setPhone(query.getString("phone"));
            student.setEmail(query.getString("email"));
            student.setRegistered(query.getString("registered"));
            student.setAddress(query.getString("address"));
            student.setProfession(query.getString("profession"));
            student.setIdNumber(query.getString("idnumber"));
            student.setPolitics(query.getString("politics"));
            student.setRegDate(query.getDate("regdate"));
            student.setState(query.getInt("state"));
            student.setIntroduction(query.getString("introduction"));
            student.setGid(query.getInt("gid"));
            students.add(student);
        }
        closeAll();
        return students;
    }

    @Override
    public int total(String stuName, String stuNo, Integer sex) throws SQLException {
        StringBuffer sql = new StringBuffer(" select count(*) from student where 1=1 and state!=4 ");
        int total=0;
        //模糊查询
        if (stuName != null && stuName!=""){
            sql.append(" and stuname like '%"+stuName+"%' ");
        }
        if (stuNo != null && stuNo!=""){
            sql.append(" and stuno like '%"+stuNo+"%' ");
        }
        if (sex!=-1 && sex != null){
            sql.append(" and sex like '%"+sex+"%' ");
        }
        ResultSet query = query(sql.toString(), null);
        while (query.next()){
            total = query.getInt(1);
        }
        closeAll();
        return total;
    }

    @Override
    public int addStudent(Student student) {
        String sql = "insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        List list = new ArrayList();
        list.add(student.getStuName());
        list.add(student.getStuNo());
        list.add(student.getSex());
        list.add(student.getPhone());
        list.add(student.getEmail());
        list.add(student.getRegistered());
        list.add(student.getAddress());
        list.add(student.getProfession());
        list.add(student.getIdNumber());
        list.add(student.getPolitics());
        list.add(student.getRegDate());
        list.add(StudentEnum.READING.type);//表示在读
        list.add(student.getIntroduction());
        list.add(student.getGid());
        int update = update(sql, list);
        closeAll();
        return update;
    }

    @Override
    public int updateStudent(Student student) {
        String sql = "update student set stuname=?,stuno=?," +
                "sex=?,phone=?,email=?,registered=?," +
                "address=?,profession=?,idnumber=?,politics=?," +
                "regdate=?,state=?,introduction=?,gid=? where stuid=?";
        List list = new ArrayList();
        list.add(student.getStuName());
        list.add(student.getStuNo());
        list.add(student.getSex());
        list.add(student.getPhone());
        list.add(student.getEmail());
        list.add(student.getRegistered());
        list.add(student.getAddress());
        list.add(student.getProfession());
        list.add(student.getIdNumber());
        list.add(student.getPolitics());
        list.add(student.getRegDate());
        list.add(StudentEnum.READING.type);//表示在读
        list.add(student.getIntroduction());
        list.add(student.getGid());
        list.add(student.getStuId());
        System.out.println(student);
        int update = update(sql, list);
        closeAll();
        return update;
    }

    @Override
    public Student selectById(int stuId) {
        String sql = "select * from student where stuid = ?";
        List list = new ArrayList();
        list.add(stuId);
        Student student = new Student();
        ResultSet query = query(sql, list);
        try {
            while (query.next()){
                student.setStuId(query.getInt("stuid"));
                student.setStuName(query.getString("stuname"));
                student.setStuNo(query.getString("stuno"));
                student.setSex(query.getInt("sex"));
                student.setPhone(query.getString("phone"));
                student.setEmail(query.getString("email"));
                student.setRegistered(query.getString("registered"));
                student.setAddress(query.getString("address"));
                student.setProfession(query.getString("profession"));
                student.setIdNumber(query.getString("idnumber"));
                student.setPolitics(query.getString("politics"));
                student.setRegDate(query.getDate("regdate"));
                student.setState(query.getInt("state"));
                student.setIntroduction(query.getString("introduction"));
                student.setGid(query.getInt("gid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int deleteById(int stuId) {
        String sql = "update student set state=? where stuid=?";
        List list = new ArrayList();
        list.add(StudentEnum.DELETE.type);
        list.add(stuId);
        int update = update(sql, list);
        closeAll();
        return update;
    }

    @Override
    public Student selectByStuNo(String stuNo) {
        String sql = "select * from student where stuNo = ?";
        List list = new ArrayList();
        list.add(stuNo);
        ResultSet query = query(sql, list);
        Student student = new Student();
        try {
            while (query.next()){
                student.setStuNo(query.getString("stuno"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }
}
